﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DreamBuilder.UI.UserControls.DataAccess.Services;
using System.Data;
using System.Data.OracleClient;
using DreamBuilder.Core;
using DreamBuilder.Core.DAO;
using CommonModule;

namespace DreamBuilder.UI.UserControls.DataAccess.Components
{
    public class OracleAttachmentDAL : AttachmentDAL
    {
        public override string AttachmentDBHelperString { get; set; }
        public override void SaveAttachment(string fileName, Byte[] attachmentContent, string attachmentID, DateTime uploadTime, 
                                                    string documentType, Hashtable addInfos)
        {
            OracleParameter attachmentContentParam = new OracleParameter(":AttachmentContent", attachmentContent);
            attachmentContentParam.OracleType = OracleType.Blob;
            OracleParameter attachmentNameParam = new OracleParameter(":AttachmentName", fileName);
            OracleParameter uploadTimeParam = new OracleParameter(":UploadTime", uploadTime);
            #region 附件实体增加更多信息 20110525
            //OracleParameter functionID = new OracleParameter(":FunctionID", addInfos["FunctionID"].ToString());//功能ID
            OracleParameter fileTypeID = new OracleParameter(":FileTypeID",addInfos!=null?addInfos["FileTypeID"]:null);//附件文件夹类型
            OracleParameter fileURL = new OracleParameter(":FileURL", addInfos != null ? addInfos["FileURL"] : null);//附件存储路径
            //OracleParameter isXml = new OracleParameter(":IsXml", addInfos["IsXml"].ToString());//是否XML代码化
            OracleParameter createUserID = new OracleParameter(":CreateUserID", addInfos != null ? addInfos["CreateUserID"] : null);//用户ID
            OracleParameter remark = new OracleParameter(":Remark", addInfos != null ? addInfos["Remark"] : null);//备注

            OracleParameter[] paramArr = new OracleParameter[] { 
                                                                    attachmentContentParam, 
                                                                    attachmentNameParam, 
                                                                    uploadTimeParam,
                                                                    fileTypeID,
                                                                    fileURL,
                                                                    createUserID,
                                                                    remark 
            };
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            DBHelper.ExecuteSql("insert into P_BT_Attachments(AttachmentID,AttachmentName,UploadTime," +
                                "AttachmentSize,DocumentType,AttachmentContent,FileTypeID," +
                                "FileURL,CreateUserID,Remark) values('" + attachmentID + "',:AttachmentName,:UploadTime," +
                                attachmentContent.Length.ToString() + ",'" + documentType + "',:AttachmentContent," + 
                                ":FileTypeID,:FileURL,:CreateUserID,:Remark)", paramArr);
            #endregion
        }

        public override DataTable GetAttachmentList(string objectID, string objectTableName, string addListValue, string deleteListValue, string FunctionID)
        {
            StringBuilder sb = new StringBuilder();
            if (FunctionID != null && FunctionID != "")
                sb.Append("select AttachmentID,ObjectTableName,ObjectID,AttachmentName,UploadTime,AttachmentSize from P_BT_Attachments where ((ObjectID = '" + objectID + "' and ObjectTableName = '" + objectTableName + "' and FunctionID='" + FunctionID + "')  ");
            else if (objectTableName != null && objectTableName != string.Empty)
            {
                sb.Append("select AttachmentID,ObjectTableName,ObjectID,AttachmentName,UploadTime,AttachmentSize from P_BT_Attachments where ((ObjectID = '" + objectID + "' and ObjectTableName = '" + objectTableName + "')  ");
            }
            else
            {
                sb.Append("select AttachmentID,ObjectTableName,ObjectID,AttachmentName,UploadTime,AttachmentSize from P_BT_Attachments where ((ObjectID = '" + objectID + "' )  ");
            }
            if (addListValue.Length > 0)
            {
                sb.Append("OR AttachmentID IN (" + addListValue.Remove(addListValue.Length - 1) + ")");
            }
            sb.Append(")");
            if (deleteListValue.Length > 0)
            {
                sb.Append(" AND AttachmentID NOT IN (" + deleteListValue.Remove(deleteListValue.Length - 1) + ")");
            }
            sb.Append(" ORDER BY UploadTime ");
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            return DBHelper.ExecuteDataSet(sb.ToString()).Tables[0];
        }

        public override DataTable GetAttachmentByID(string attachmentID)
        {
            string sql = "select AttachmentName,AttachmentContent,DocumentType,ATTACHMENTSIZE from P_BT_Attachments where AttachmentID = '" + attachmentID + "'";
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
               DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            return DBHelper.ExecuteDataSet(sql).Tables[0];
        }
        public override DataTable GetSaveDataTable(string ObjectID, string ObjectTableName, string addListValue, string deleteListValue)
        {
            DataTable dtSave = new DataTable();

            if (addListValue.Length > 0)
            {
                string sqlstr = "select AttachmentID,'" + ObjectTableName + "'as ObjectTableName,'" + ObjectID + "' as ObjectID,'" + (int)ROWSTATUS.STATUS_Modify + "' as RowStatus from P_BT_Attachments  where AttachmentID in (" + addListValue.Remove(addListValue.Length - 1) + ")";
                if (!string.IsNullOrEmpty(AttachmentDBHelperString))
                {
                    DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
                }
                DataTable dtUpdate = DBHelper.ExecuteDataSet(sqlstr).Tables[0];
                dtSave = dtUpdate.Copy();
                
            }

            if (deleteListValue.Length > 0)
            {
                string sqlstr = "select AttachmentID,'" + ObjectTableName + "'as ObjectTableName,'" + ObjectID + "'as ObjectID,'" + (int)ROWSTATUS.STATUS_Delete + "' as RowStatus from P_BT_Attachments  where AttachmentID in (" + deleteListValue.Remove(deleteListValue.Length - 1) + ")";
                if (!string.IsNullOrEmpty(AttachmentDBHelperString))
                {
                    DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
                }
                DataTable dtDelete = DBHelper.ExecuteDataSet(sqlstr).Tables[0];
                if (dtSave.Rows.Count > 0)
                {
                    dtSave.Merge(dtDelete);
                }
                else
                {
                    dtSave = dtDelete.Copy();
                }
            }

            dtSave.TableName = "P_BT_Attachments";
            dtSave.PrimaryKey = new DataColumn[] { dtSave.Columns["AttachmentID"] };
            return dtSave;
        }

        public override void ClearExpireAttachment()
        {
            string sql = " delete from p_bt_attachments where objecttablename is null and objectid is null and uploadtime < sysdate-1 ";
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            DBHelper.ExecuteSql(sql);
        }

        public override bool HaveAttachments(string tableCode, string pkValue)
        {
            string sql = " select * from P_BT_Attachments where ObjectTableName = '" + tableCode + "' and  ObjectID = '" + pkValue + "' ";
            if (!string.IsNullOrEmpty(AttachmentDBHelperString))
            {
                DBHelper = DBHelperFactory.CreateInstance(AttachmentDBHelperString);
            }
            DataSet ds = DBHelper.ExecuteDataSet(sql);
            bool haveAttachments = false;
            if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            {
                haveAttachments = true;
            }
            return haveAttachments;
        }
    }
}
